const mysql = require('mysql')
const debug = require('../utils/env').debug
const {
  host, user, password, database
} = require('./config')

// 模糊查询
function orLike(req, key) {
  return req.body[key] ? ` where ${key} LIKE '%${req.body[key]}%'` : ''
}

// 分页
function orLimit(req) {
  return `LIMIT ${((req.body.pageNum||1)-1)*10}, ${req.body.pageSize||10000}`
}

// 查询某张表的数量
let queryTotal = (tableName) => {
  return new Promise(async (resolve, reject) => {
    let result = await querySql(`SELECT COUNT(*) as total FROM ${tableName}`)
    resolve(result[0].total)
  })
}
// 查询某个sql查出来的数量
let querySqlTotal = (sql) => {
  return new Promise(async (resolve, reject) => {
    let result = await querySql(sql)
    resolve(result.length)
  })
}

function andLike(where, k, v) {
  if (where === 'where') {
    return where + ` ${k} like '%${v}%'`
  } else {
    return where + ` and ${k} like '%${v}%'`
  }
}

function or(where, k, v) {
  if (where === 'where') {
    return where + ` ${k}='${v}'`
  } else {
    return where + ` or ${k}='${v}'`
  }
}

function and(where, k, v) {
  if (where === 'where') {
    return where + ` ${k}='${v}'`
  } else {
    return where + ` and ${k}='${v}'`
  }
}

function connect() {
  return mysql.createConnection({
    host,
    user,
    password,
    database,
    multipleStatements: true
  })
}

function querySql(sql) {
  const conn = connect()
  debug && console.log(sql)
  return new Promise((resolve, reject) => {
    try {
      conn.query(sql, (err, results) => {
        if (err) {
          debug && console.log('查询失败，原因:' + JSON.stringify(err))
          reject(err)
        } else {
          debug && console.log('查询成功', JSON.stringify(results))
          resolve(results)
        }
      })
    } catch (e) {
      reject(e)
    } finally {
      conn.end()
    }
  })
}

function querySqlList(sql, onSuccess, onFail) {
  const conn = connect()
  debug && console.log(sql)
  const resultList = []
  let index = 0

  function next() {
    index++
    if (index < sql.length) {
      query()
    } else {
      conn.end()
      onSuccess && onSuccess(resultList)
    }
  }

  function query() {
    conn.query(sql[index], (err, results) => {
      if (err) {
        console.log('操作失败，原因:' + JSON.stringify(err))
        onFail && onFail()
      } else {
        // debug && console.log('操作成功', JSON.stringify(results))
        resultList.push(results)
        next()
      }
    })
  }

  query()
}

function queryOne(sql) {
  return new Promise((resolve, reject) => {
    querySql(sql)
      .then(results => {
        if (results && results.length > 0) {
          resolve(results[0])
        } else {
          resolve(null)
        }
      })
      .catch(error => {
        reject(error)
      })
  })
}

function insert(sql, primaryKey = '') {
  return new Promise((resolve, reject) => {
    const conn = connect()
    conn.query(sql, async (err, result) => {
      if (err) {
        reject(err)
      } else {
        let tableName = sql.replace(/\s*/g,"").match(/into(\S*)values/)[1]
        let temp = true
        try {
          temp = await queryOne(`select * from ${tableName} where ${primaryKey || 'id'} = ${result.insertId}`)
        } catch (error) {

        }

        resolve(temp)
      }
    })
  })
}

function deleteData(sql) {
  return new Promise((resolve, reject) => {
    const conn = connect()
    conn.query(sql, (err, result) => {
      if (err) {
        reject(err)
      } else {
        resolve(result)
      }
    })
  })
}

function update(sql) {
  return new Promise((resolve, reject) => {
    const conn = connect()
    conn.query(sql, (err, result) => {
      if (err) {
        reject(err)
      } else {
        resolve(result)
      }
    })
  })
}

module.exports = {
  connect,
  querySql,
  querySqlList,
  orLike,
  querySqlTotal,
  queryTotal,
  orLimit,
  or,
  insert,
  queryOne,
  andLike,
  and,
  update,
  deleteData
}
